﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using DBUtility;

namespace SQLProfileDAL
{
    public class iBookShopProfileProvider : IProfileDAL.IiBookShopProfileProvider
    {
        // Contst matching System.Web.Profile.ProfileAuthenticationOption.Anonymous
        private const int AUTH_ANONYMOUS = 0;

        // Contst matching System.Web.Profile.ProfileAuthenticationOption.Authenticated
        private const int AUTH_AUTHENTICATED = 1;

        // Contst matching System.Web.Profile.ProfileAuthenticationOption.All
        private const int AUTH_ALL = 2;

        public IList<CartItemInfo> GetCartItems(string userName, string appName)
        {
            string sqlSelect = "SELECT C.ItemISBN,C.ItemName,C.UnitPrice,C.Quantity FROM CartItems C INNER JOIN Profiles P ON C.ProfileId = P.ProfileId WHERE P.Username = @Username AND ApplicationName = @ApplicationName";
            SqlParameter[] parms = {
                new SqlParameter("@Username",SqlDbType.VarChar,256),
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256)};
            parms[0].Value = userName;
            parms[1].Value = appName;

            IList<CartItemInfo> cartItems = new List<CartItemInfo>();

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlSelect, parms);
            while (dr.Read())
            {
                CartItemInfo cartItem = new CartItemInfo(dr.GetString(0), dr.GetString(1), dr.GetDouble(2), dr.GetInt32(3));
                cartItems.Add(cartItem);
            }
            dr.Close();
            return cartItems;
        }

        public void SetCartItems(int profileId, ICollection<CartItemInfo> cartItems)
        {
            string sqlDelete = "DELETE FROM CartItems WHERE ProfileId = @ProfileId";
            SqlParameter parmDelete = new SqlParameter("@ProfileId", SqlDbType.Int);
            parmDelete.Value = profileId;

            if (cartItems.Count > 0)
            {
                string sqlInsert = "INSERT INTO CartItems (ProfileId,ItemISBN,ItemName,UnitPrice,Quantity) VALUES (@ProfileId,@ItemISBN,@ItemName,@UnitPrice,@Quantity)";
                SqlParameter[] parmsInsert = {
                    new SqlParameter("@ProfileId",SqlDbType.Int),
                    new SqlParameter("@ItemISBN",SqlDbType.NChar,13),
                    new SqlParameter("@ItemName",SqlDbType.VarChar,100),
                    new SqlParameter("@UnitPrice",SqlDbType.Float),
                    new SqlParameter("@Quantity",SqlDbType.Int)};
                parmsInsert[0].Value = profileId;


                SqlConnection conn = new SqlConnection(SqlHelper.iBookShopProfileConnStr);
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlDelete, parmDelete);

                    foreach (CartItemInfo cartItem in cartItems)
                    {
                        parmsInsert[1].Value = cartItem.ItemISBN;
                        parmsInsert[2].Value = cartItem.ItemName;
                        parmsInsert[3].Value = cartItem.UnitPrice;
                        parmsInsert[4].Value = cartItem.Quantity;
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parmsInsert);
                    }
                    trans.Commit();
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    throw new ApplicationException(e.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlDelete, parmDelete);
            }
        }

        public void UpdateActivityDates(string userName, string appName)
        {
            DateTime updatedDatetime = DateTime.Now;
            string sqlUpdate = "UPDATE Profiles SET LastUpdatedDate = @LastUpdatedDate WHERE Username = @Username AND ApplicationName = @ApplicationName";
            
            SqlParameter[] parms = {
                new SqlParameter("@LastUpdatedDate",SqlDbType.DateTime),
                new SqlParameter("@Username",SqlDbType.VarChar,256),
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256)};

            parms[0].Value = updatedDatetime;
            parms[1].Value = userName;
            parms[2].Value = appName;

            SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlUpdate, parms);
        }

        public int GetProfileId(string userName, bool isAuthenticated, bool ignoreAuthenticationType, string appName)
        {
            StringBuilder sqlSelect = new StringBuilder("SELECT ProfileId FROM Profiles WHERE Username = @Username AND ApplicationName = @ApplicationName");
            SqlParameter[] parms = {
                new SqlParameter("@Username",SqlDbType.VarChar,256),
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256)};
            parms[0].Value = userName;
            parms[1].Value = appName;

            if (!ignoreAuthenticationType)
            {
                sqlSelect.Append(" AND IsAnonymous = @IsAnonymous");
                Array.Resize<SqlParameter>(ref parms, parms.Length + 1);
                parms[2] = new SqlParameter("@isAnonymous",SqlDbType.Bit);
                parms[2].Value = !isAuthenticated;
            }

            int profileId = 0;

            //object retVal = null;
            //retVal = SqlHelper.ExecuteScalar(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlSelect.ToString(), parms);

            //if (retVal == null)
            //{
            //    profileId = CreateProfileForUser(userName, isAuthenticated, appName);
            //}
            //else
            //{
            //    profileId = Convert.ToInt32(retVal);
            //}

            object retVal = null;
            retVal = SqlHelper.ExecuteScalar(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlSelect.ToString(), parms);

            if (retVal != null)
            {
                profileId = Convert.ToInt32(retVal);
            }

            return profileId;
        }

        public int CreateProfileForUser(string userName, bool isAuthenticated, string appName)
        {
            DateTime updatedDatetime = DateTime.Now;

            string sqlInsert = "INSERT INTO Profiles (Username,ApplicationName,isAnonymous,LastUpdatedDate) VALUES (@Username,@ApplicationName,@isAnonymous,@LastUpdatedDate); SELECT @@IDENTITY;";
            
            SqlParameter[] parms = {
                new SqlParameter("@Username",SqlDbType.VarChar,256),
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256),
                new SqlParameter("@isAnonymous",SqlDbType.Bit),
                new SqlParameter("@LastUpdatedDate",SqlDbType.DateTime)};

            parms[0].Value = userName;
            parms[1].Value = appName;
            parms[2].Value = !isAuthenticated;
            parms[3].Value = updatedDatetime;

            int profileId = 0;
            int.TryParse(SqlHelper.ExecuteScalar(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlInsert, parms).ToString(), out profileId);

            return profileId;
        }

        public IList<string> GetInactiveProfiles(int authenticationOption, DateTime userInactiveSinceDate, string appName)
        {
            StringBuilder sqlSelect = new StringBuilder("SELECT Username FROM Profiles WHERE ApplicationName = @ApplicationName AND LastUpdatedDate <= @LastUpdatedDate ");

            SqlParameter[] parms = {
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256),
                new SqlParameter("@LastUpdatedDate",SqlDbType.DateTime)};

            parms[0].Value = appName;
            parms[1].Value = userInactiveSinceDate;

            switch (authenticationOption)
            {
                case AUTH_ANONYMOUS:
                    sqlSelect.Append(" AND IsAnonymous = @IsAnonymous");
                    Array.Resize<SqlParameter>(ref parms, parms.Length + 1);
                    parms[2] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
                    parms[2].Value = true;
                    break;
                case AUTH_AUTHENTICATED:
                    sqlSelect.Append(" AND IsAnonymous = @IsAnonymous");
                    Array.Resize<SqlParameter>(ref parms, parms.Length + 1);
                    parms[2] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
                    parms[2].Value = false;
                    break;
                default:
                    break;
            }

            IList<string> usernames = new List<string>();

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlSelect.ToString(), parms);

            while (dr.Read())
            {
                usernames.Add(dr.GetString(0));
            }

            dr.Close();
            return usernames;
        }

        public bool DeleteProfile(string userName, string appName)
        {
            int profileId = GetProfileId(userName, false, true, appName);

            string sqlDelete = "DELETE FROM Profiles WHERE ProfileId = @ProfileId";
            SqlParameter parms = new SqlParameter("@ProfileId", SqlDbType.Int);
            parms.Value = profileId;

            int numDeleted = SqlHelper.ExecuteNonQuery(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlDelete, parms);

            if (numDeleted <= 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public IList<UserProfileInfo> GetProfileInfo(int authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, string appName, out int totalRecords)
        {
            StringBuilder sqlSelect1 = new StringBuilder("SELECT COUNT(*) FROM Profiles WHERE ApplicationName = @ApplicationName");
            SqlParameter[] parms1 ={
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256)};
            parms1[0].Value = appName;

            StringBuilder sqlSelect2 = new StringBuilder("SELECT Username, LastUpdatedDate, IsAnonymous FROM Profiles WHERE ApplicationName = @ApplicationName");
            SqlParameter[] parms2 ={
                new SqlParameter("@ApplicationName",SqlDbType.VarChar,256)};
            parms2[0].Value = appName;

            int arraySize;

            if (usernameToMatch != null)
            {
                arraySize = parms1.Length;

                sqlSelect1.Append(" AND Username LIKE @Username ");
                Array.Resize<SqlParameter>(ref parms1, arraySize + 1);
                parms1[arraySize] = new SqlParameter("@Username", SqlDbType.VarChar, 256);
                parms1[arraySize].Value = usernameToMatch;

                sqlSelect2.Append(" AND Username LIKE @Username ");
                Array.Resize<SqlParameter>(ref parms2, arraySize + 1);
                parms2[arraySize] = new SqlParameter("@Username", SqlDbType.VarChar, 256);
                parms2[arraySize].Value = usernameToMatch;
            }

            if (userInactiveSinceDate != null)
            {
                arraySize = parms1.Length;

                sqlSelect1.Append(" AND LastUpdatedDate >= @LastUpdatedDate ");
                Array.Resize<SqlParameter>(ref parms1, arraySize + 1);
                parms1[arraySize] = new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime);
                parms1[arraySize].Value = (DateTime)userInactiveSinceDate;

                sqlSelect2.Append(" AND LastUpdatedDate >= @LastUpdatedDate ");
                Array.Resize<SqlParameter>(ref parms2, arraySize + 1);
                parms2[arraySize] = new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime);
                parms2[arraySize].Value = (DateTime)userInactiveSinceDate;
            }

            if (authenticationOption != AUTH_ALL)
            {
                arraySize = parms1.Length;

                sqlSelect1.Append(" AND IsAnonymous = @IsAnonymous ");
                Array.Resize<SqlParameter>(ref parms1, arraySize + 1);
                parms1[arraySize] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);

                sqlSelect2.Append(" AND IsAnonymous = @IsAnonymous ");
                Array.Resize<SqlParameter>(ref parms2, arraySize + 1);
                parms2[arraySize] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);

                switch (authenticationOption)
                {
                    case AUTH_ANONYMOUS:
                        parms1[arraySize].Value = true;
                        parms2[arraySize].Value = true;
                        break;
                    case AUTH_AUTHENTICATED:
                        parms1[arraySize].Value = false;
                        parms2[arraySize].Value = false;
                        break;
                    default:
                        break;
                }
            }

            IList<UserProfileInfo> profiles = new List<UserProfileInfo>();

            totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.iBookShopProfileConnStr, CommandType.Text, sqlSelect1.ToString(), parms1);

            if (totalRecords <= 0)
            {
                return profiles;
            }

            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.iBookShopProfileConnStr,CommandType.Text,sqlSelect2.ToString(),parms2);
            while(dr.Read())
            {
                UserProfileInfo profile = new UserProfileInfo(dr.GetString(0),dr.GetDateTime(1),dr.GetBoolean(2));
                profiles.Add(profile);
            }
            dr.Close();

            return profiles;
        }
    }
}
